﻿using System;
using System.IO;
using System.Data;
using System.Text.RegularExpressions;
using System.Web.UI.WebControls;

namespace HiauPdm.Bll
{
    /// <summary>
    /// 产品设计编辑
    /// </summary>
    public class BCpSjBj
    {
        #region 产品设计代号登记

        /// <summary>
        /// 根据[登记人]获得产品设计代号
        /// </summary>
        public static DataSet GetCpSjhDjByDjr(string sDjr)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.代号,A.名称,A.规格型号,A.备注,A.登记人,A.登记时间 FROM 产品设计 A WHERE A.状态=0 AND NOT EXISTS(SELECT 1 FROM 产品文档 B WHERE B.产品ID=A.ID) AND NOT EXISTS(SELECT 1 FROM 产品明细 B WHERE B.产品ID=A.ID) AND NOT EXISTS(SELECT 1 FROM 产品外购 B WHERE B.产品ID=A.ID) AND (@P0='' OR A.登记人=@P0) ORDER BY A.ID DESC", sDjr);
            }
        }

        /// <summary>
        /// 登记产品设计代号
        /// </summary>
        public static string SaveCpSjh(string sDh, string sLb, string sMc, string sGgxh, string sDw, string sBz, string sDjr)
        {
            if (sDh == string.Empty || sMc.Trim() == string.Empty || sDw.Trim() == string.Empty)
            {
                return "产品设计的代号、名称、计量单位不能为空！";
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("INSERT INTO 产品设计(代号,类别,名称,规格型号,单位,备注,登记人,登记时间,编辑人,编辑时间,状态) SELECT @P0+'.'+REPLACE(STR(ISNULL(MAX(SUBSTRING(代号,11,4)),0)+1,4),' ','0')+'v01',@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P6,@P7,0 FROM 产品设计 WHERE 代号 LIKE @P0+'%'", sDh, sLb, sMc.Trim(), sGgxh.Trim(), sDw.Trim(), sBz.Trim(), sDjr, DateTime.Now) <= 0)
                {
                    return "登记产品设计代号失败！";
                }
                return null;
            }
        }

        /// <summary>
        /// 根据[产品设计ID]删除产品设计代号
        /// </summary>
        public static string DelCpSjh(int iCpSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("DELETE FROM 产品设计 WHERE ID=@P0 AND 状态=0 AND NOT EXISTS(SELECT 1 FROM 产品文档 WHERE 产品文档.产品ID=产品设计.ID) AND NOT EXISTS(SELECT 1 FROM 产品明细 WHERE 产品明细.产品ID=产品设计.ID OR 产品明细.零件ID=产品设计.ID) AND NOT EXISTS(SELECT 1 FROM 产品外购 WHERE 产品外购.产品ID=产品设计.ID) AND NOT EXISTS(SELECT 1 FROM 物资入库 WHERE 物资入库.物资编号=产品设计.代号)", iCpSjId) <= 0)
                {
                    return "删除产品设计代号失败，可能存在引用！";
                }
                return null;
            }
        }

        #endregion

        #region 产品设计编辑

        /// <summary>
        /// 根据[产品设计ID]创建新版产品设计
        /// </summary>
        public static string CopyNewCpSj(int iCpSjId, string sBjr, out int iNewCpSjId)
        {
            iNewCpSjId = 0;
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                using (DSqlTrans daTrans = da.BeginTrans())
                {
                    DataSet ds = daTrans.ExecuteQuery("INSERT INTO 产品设计(代号,类别,名称,规格型号,单位,备注,原代号,登记人,登记时间,编辑人,编辑时间,状态) SELECT (SELECT LEFT(A.代号,15)+REPLACE(STR(MAX(RIGHT(B.代号,2))+1,2),' ','0') FROM 产品设计 B WHERE LEFT(B.代号,15)=LEFT(A.代号,15)),A.类别,A.名称,A.规格型号,A.单位,A.备注,A.代号,@P1,@P2,@P1,@P2,0 FROM 产品设计 A WHERE A.ID=@P0 AND A.代号=(SELECT MAX(B.代号) FROM 产品设计 B WHERE LEFT(B.代号,15)=LEFT(A.代号,15)); SELECT @@IDENTITY", iCpSjId, sBjr, DateTime.Now);
                    if (ds == null || ds.Tables.Count != 1 || ds.Tables[0].Rows[0][0] == DBNull.Value)
                    {
                        return "创建产品设计的新版本失败！";
                    }
                    iNewCpSjId = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                    if (daTrans.Execute("INSERT INTO 产品文档(产品ID,文档名称,备注,路径,编辑人,编辑时间) SELECT @P1,文档名称,备注,路径,编辑人,编辑时间 FROM 产品文档 WHERE 产品ID=@P0", iCpSjId, iNewCpSjId) < 0)
                    {
                        return "创建产品设计的新版本失败！";
                    }
                    if (daTrans.Execute("INSERT INTO 产品明细(产品ID,零件ID,装入数量,装入代号,备注,编辑人,编辑时间) SELECT @P1,零件ID,装入数量,装入代号,备注,编辑人,编辑时间 FROM 产品明细 WHERE 产品ID=@P0", iCpSjId, iNewCpSjId) < 0)
                    {
                        return "创建产品设计的新版本失败！";
                    }
                    if (daTrans.Execute("INSERT INTO 产品外购(产品ID,购件ID,装入数量,装入代号,备注,编辑人,编辑时间) SELECT @P1,购件ID,装入数量,装入代号,备注,编辑人,编辑时间 FROM 产品外购 WHERE 产品ID=@P0", iCpSjId, iNewCpSjId) < 0)
                    {
                        return "创建产品设计的新版本失败！";
                    }
                    if (daTrans.Execute("UPDATE 产品明细 SET 零件ID=@P1 WHERE 零件ID=@P0", iCpSjId, iNewCpSjId) < 0)
                    {
                        return "创建产品设计的新版本失败！";
                    }
                    if (!daTrans.Commit())
                    {
                        return "创建产品设计的新版本失败！";
                    }
                    return null;
                }
            }
        }

        /// <summary>
        /// 根据[登记人]获得产品设计编辑总记录数
        /// </summary>
        public static DataSet GetCpSjBjCountByDjr(int iDl, string sWhere, string sDjr)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT COUNT(1) FROM 产品设计 A WHERE A.状态=0 AND (@P0=-1 OR (CASE WHEN (SUBSTRING(A.代号,5,1) BETWEEN 1 AND 4) THEN 1 ELSE 0 END)=@P0) AND (@P1='' OR A.登记人=@P1) AND {0}", BGlobal.BuildLike(sWhere, "A.代号", "A.名称", "A.规格型号")), iDl, sDjr);
            }
        }

        /// <summary>
        /// 根据[登记人]获得产品设计编辑
        /// </summary>
        public static DataSet GetCpSjBjByDjr(int iDl, string sWhere, string sDjr, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.代号,A.名称,A.规格型号,A.备注,A.登记人,(SELECT COUNT(1) FROM 产品文档 B WHERE B.产品ID=A.ID) 文档,(SELECT COUNT(1) FROM 产品明细 B WHERE B.产品ID=A.ID) 零部件,(SELECT COUNT(1) FROM 产品外购 B WHERE B.产品ID=A.ID) 外购件,CASE WHEN (SUBSTRING(A.代号,5,1) BETWEEN 1 AND 4) THEN 2 WHEN (SUBSTRING(A.代号,5,1) BETWEEN 5 AND 6) THEN 1 ELSE 0 END 大类 FROM 产品设计 A WHERE A.状态=0 AND (@P0=-1 OR (CASE WHEN (SUBSTRING(A.代号,5,1) BETWEEN 1 AND 4) THEN 1 ELSE 0 END)=@P0) AND (@P1='' OR A.登记人=@P1) AND {2} AND A.ID NOT IN(SELECT TOP {1} B.ID FROM 产品设计 B WHERE B.状态=0 AND (@P0=-1 OR (CASE WHEN (SUBSTRING(B.代号,5,1) BETWEEN 1 AND 4) THEN 1 ELSE 0 END)=@P0) AND (@P1='' OR B.登记人=@P1) AND {3} ORDER BY B.代号) ORDER BY A.代号", iPageSize, iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "A.代号", "A.名称", "A.规格型号"), BGlobal.BuildLike(sWhere, "B.代号", "B.名称", "B.规格型号")), iDl, sDjr);
            }
        }

        /// <summary>
        /// 根据[登记人]获得产品设计编辑
        /// </summary>
        public static DataSet GetCpSjBjTree(int iDl, string sWhere, string sDjr)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT A.ID,A.代号,A.名称,A.规格型号,A.状态,CAST((SELECT COUNT(1) FROM 产品明细 B WHERE B.产品ID=A.ID) AS BIT) 含零部件 FROM 产品设计 A WHERE A.状态=0 AND (SUBSTRING(A.代号,5,1) BETWEEN 1 AND 6) AND (@P0=-1 OR (CASE WHEN (SUBSTRING(A.代号,5,1) BETWEEN 1 AND 4) THEN 1 ELSE 0 END)=@P0) AND (@P1='' OR A.登记人=@P1) AND {0} ORDER BY A.代号", BGlobal.BuildLike(sWhere, "A.代号", "A.名称", "A.规格型号")), iDl, sDjr);
            }
        }

        /// <summary>
        /// 根据[产品设计ID]获得子产品设计编辑
        /// </summary>
        public static DataSet GetCpSjBjTree(int iCpSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.代号,A.名称,A.规格型号,A.状态,CAST((SELECT COUNT(1) FROM 产品明细 C WHERE C.产品ID=A.ID) AS BIT) 含零部件  FROM 产品设计 A, 产品明细 B WHERE A.ID=B.零件ID AND B.产品ID=@P0 ORDER BY A.代号", iCpSjId);
            }
        }

        /// <summary>
        /// 根据[产品设计ID]获得产品设计编辑
        /// </summary>
        public static DataSet GetCpSjBjBySjId(int iCpSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.代号,A.类别,A.名称,A.规格型号,A.单位,A.备注,A.登记人,A.编辑人,A.编辑时间,CASE WHEN (SUBSTRING(A.代号,5,1) BETWEEN 1 AND 4) THEN 2 WHEN (SUBSTRING(A.代号,5,1) BETWEEN 5 AND 6) THEN 1 ELSE 0 END 大类 FROM 产品设计 A WHERE A.状态=0 AND A.ID=@P0", iCpSjId);
            }
        }

        /// <summary>
        /// 根据[产品设计ID]获得产品设计文档
        /// </summary>
        public static DataSet GetCpSjWdBySjId(int iCpSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT ID,文档名称,备注,路径,编辑人,编辑时间 FROM 产品文档 WHERE 产品ID=@P0 ORDER BY ID", iCpSjId);
            }
        }

        /// <summary>
        /// 保存产品设计文档
        /// </summary>
        public static string SaveCpSjWd(int iCpSjId, string sMc, string sGgxh, string sDw, string sBz, string sBjr, GridView gCpSjWd)
        {
            if (sMc.Trim() == string.Empty || sDw.Trim() == string.Empty)
            {
                return "产品设计的名称、计量单位不能为空！";
            }
            for (int i = 0; i < gCpSjWd.Rows.Count; i++)
            {
                if (((TextBox)gCpSjWd.Rows[i].Cells[1].Controls[1]).Text.Trim() == string.Empty && gCpSjWd.DataKeys[i].Value.ToString() != string.Empty)
                {
                    return "产品设计文档的名称不能为空！";
                }
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("UPDATE 产品设计 SET 名称=@P1,规格型号=@P2,单位=@P3,备注=@P4,编辑人=@P5,编辑时间=@P6 WHERE ID=@P0 AND 状态=0", iCpSjId, sMc.Trim(), sGgxh.Trim(), sDw.Trim(), sBz.Trim(), sBjr, DateTime.Now) <= 0)
                {
                    return "保存产品设计文档失败，可能已提交！";
                }
                string[] sFilePathArr = new string[gCpSjWd.Rows.Count];
                for (int i = 0; i < gCpSjWd.Rows.Count; i++)
                {
                    FileUpload fUpload = (FileUpload)gCpSjWd.Rows[i].Cells[1].Controls[3];
                    if (fUpload.PostedFile == null || fUpload.PostedFile.FileName == string.Empty || ((TextBox)gCpSjWd.Rows[i].Cells[1].Controls[1]).Text.Trim() == string.Empty)
                    {
                        sFilePathArr[i] = gCpSjWd.DataKeys[i].Values[1].ToString();
                    }
                    else
                    {
                        string sFileKzm = (fUpload.PostedFile.FileName.LastIndexOf(".") > 0) ? BGlobal.StringToHex(fUpload.PostedFile.FileName.Substring(fUpload.PostedFile.FileName.LastIndexOf(".") + 1).ToLower()) : string.Empty;
                        sFilePathArr[i] = string.Format("Cp{0}{1}_{2}", iCpSjId.ToString("X5"), DateTime.Now.ToString("yyyyMMddHHmmssffffff"), sFileKzm);
                        try
                        {
                            fUpload.PostedFile.SaveAs(string.Format("{0}{1}", BGlobal.AttachmentPath, sFilePathArr[i]));
                        }
                        catch
                        {
                            sFilePathArr[i] = gCpSjWd.DataKeys[i].Values[1].ToString();
                        }
                    }
                    // 去除文档名称中的代号
                    string sDocName = Regex.Replace(((TextBox)gCpSjWd.Rows[i].Cells[1].Controls[1]).Text.Trim(), @"^HIAU[\d\.\-_ Vv]+\d{2}_?", "").Trim();
                    if (sDocName == string.Empty) { sDocName = sMc; }
                    // 写入数据库
                    if (gCpSjWd.DataKeys[i].Value.ToString() != string.Empty)
                    {
                        da.Execute("UPDATE 产品文档 SET 文档名称=@P1,备注=@P2,路径=@P3,编辑人=@P4,编辑时间=@P5 WHERE ID=@P0 AND (文档名称<>@P1 OR 备注<>@P2 OR 路径<>@P3)", (int)gCpSjWd.DataKeys[i].Value, sDocName, ((TextBox)gCpSjWd.Rows[i].Cells[2].Controls[1]).Text.Trim(), sFilePathArr[i], sBjr, DateTime.Now);
                    }
                    else if (sFilePathArr[i] != string.Empty)
                    {
                        da.Execute("INSERT INTO 产品文档(产品ID,文档名称,备注,路径,编辑人,编辑时间) VALUES(@P0,@P1,@P2,@P3,@P4,@P5)", iCpSjId, sDocName, ((TextBox)gCpSjWd.Rows[i].Cells[2].Controls[1]).Text.Trim(), sFilePathArr[i], sBjr, DateTime.Now);
                    }
                }
                return null;
            }
        }

        /// <summary>
        /// 根据[产品设计文档ID]删除产品设计文档
        /// </summary>
        public static string DelCpSjWd(int iCpSjWdId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("DELETE FROM 产品文档 WHERE ID=@P0 AND EXISTS(SELECT 1 FROM 产品设计 WHERE 产品设计.ID=产品文档.产品ID AND 产品设计.状态=0)", iCpSjWdId) <= 0)
                {
                    return "删除产品设计文档失败，可能已提交！";
                }
                return null;
            }
        }

        /// <summary>
        /// 导入产品设计明细表
        /// </summary>
        private static bool ImportCpSjMxDb(int iCpSjId, string sDh, int iZysl, string sZydh, string sBjr)
        {
            if (sDh.ToUpper().IndexOf("HIAU") == 0)
            {
                // 零部件导入
                using (DSql da = new DSql(BGlobal.DbConnString))
                {
                    if (da.Execute("UPDATE 产品明细 SET 装入数量=@P1,装入代号=@P2,编辑人=@P3,编辑时间=@P4,编辑标志=1 WHERE 产品ID=@P0 AND 零件ID=(SELECT A.ID FROM 产品设计 A WHERE A.代号=@P5) AND EXISTS(SELECT 1 FROM 产品设计 A WHERE A.ID=@P0 AND A.状态=0)", iCpSjId, iZysl, sZydh, sBjr, DateTime.Now, sDh) < 1)
                    {
                        DataSet ds = da.ExecuteQuery("SELECT A.ID FROM 产品设计 A WHERE A.代号=@P1 AND NOT EXISTS(SELECT 1 FROM 产品明细 B WHERE B.产品ID=@P0 AND B.零件ID=A.ID) AND EXISTS(SELECT 1 FROM 产品设计 B WHERE B.ID=@P0 AND B.状态=0)", iCpSjId, sDh);
                        if (((ds != null) && (ds.Tables[0].Rows.Count == 1)) && !IsParentCpSjLbj(iCpSjId, (int)ds.Tables[0].Rows[0][0]))
                        {
                            if (da.Execute("INSERT INTO 产品明细(产品ID,零件ID,装入数量,装入代号,备注,编辑人,编辑时间,编辑标志) SELECT @P0,A.ID,@P1,@P2,'',@P3,@P4,1 FROM 产品设计 A WHERE A.代号=@P5 AND NOT EXISTS(SELECT 1 FROM 产品明细 B WHERE B.产品ID=@P0 AND B.零件ID=A.ID) AND EXISTS(SELECT 1 FROM 产品设计 B WHERE B.ID=@P0 AND B.状态=0 AND (SUBSTRING(B.代号,5,1) BETWEEN 1 AND 6))", iCpSjId, iZysl, sZydh, sBjr, DateTime.Now, sDh) < 1)
                            {
                                return false;
                            }
                            if (IsParentCpSjLbj(iCpSjId, (int)ds.Tables[0].Rows[0][0]))
                            {
                                da.Execute("DELETE FROM 产品明细 WHERE 产品ID=@P0 AND 零件ID=@P1)", iCpSjId, (int)ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
            }
            else
            {
                // 外购件导入
                using (DSql da = new DSql(BGlobal.DbConnString))
                {
                    if (da.Execute("UPDATE 产品外购 SET 装入数量=@P1,装入代号=@P2,编辑人=@P3,编辑时间=@P4,编辑标志=1 WHERE 产品ID=@P0 AND 购件ID=(SELECT A.ID FROM 购件目录 A WHERE A.代号=@P5) AND EXISTS(SELECT 1 FROM 产品设计 A WHERE A.ID=@P0 AND A.状态=0)", iCpSjId, iZysl, sZydh, sBjr, DateTime.Now, sDh) < 1)
                    {
                        if (da.Execute("INSERT INTO 产品外购(产品ID,购件ID,装入数量,装入代号,备注,编辑人,编辑时间,编辑标志) SELECT @P0,A.ID,@P1,@P2,'',@P3,@P4,1 FROM 购件目录 A WHERE A.代号=@P5 AND NOT EXISTS(SELECT 1 FROM 产品外购 B WHERE B.产品ID=@P0 AND B.购件ID=A.ID) AND EXISTS(SELECT 1 FROM 产品设计 B WHERE B.ID=@P0 AND B.状态=0 AND (SUBSTRING(B.代号,5,1) BETWEEN 1 AND 4))", iCpSjId, iZysl, sZydh, sBjr, DateTime.Now, sDh) < 1)
                        {
                            return false;
                        }
                    }
                }
            }
            return true;
        }

        /// <summary>
        /// 导入产品设计明细表
        /// </summary>
        private static string ImportCpSjMxZydh(string sDh, DataTable dtZydh)
        {
            dtZydh.DefaultView.RowFilter = string.Format("代号='{0}' AND 装入代号<>''", sDh);
            if (dtZydh.DefaultView.Count <= 0) { return string.Empty; }
            dtZydh.DefaultView.Sort = string.Empty;
            for (int i = 0; i < dtZydh.DefaultView.Count; i++)
            {
                dtZydh.DefaultView[i]["装入代号1"] = Regex.Replace((string)dtZydh.DefaultView[i]["装入代号"], @"\d+$", "");
                if ((string)dtZydh.DefaultView[i]["装入代号"] != (string)dtZydh.DefaultView[i]["装入代号1"])
                {
                    dtZydh.DefaultView[i]["装入代号2"] = Convert.ToInt32(((string)dtZydh.DefaultView[i]["装入代号"]).Replace((string)dtZydh.DefaultView[i]["装入代号1"], ""));
                }
            }
            dtZydh.DefaultView.Sort = "装入代号1,装入代号2";
            string sImportZydh = (string)dtZydh.DefaultView[0]["装入代号"];
            string sZydh1 = (string)dtZydh.DefaultView[0]["装入代号1"];
            int iZydh2 = (int)dtZydh.DefaultView[0]["装入代号2"];
            int iZydh2Kd = 1;
            for (int i = 1; i < dtZydh.DefaultView.Count; i++)
            {
                if (sZydh1 != (string)dtZydh.DefaultView[i]["装入代号1"] || iZydh2 + iZydh2Kd != (int)dtZydh.DefaultView[i]["装入代号2"])
                {
                    if (iZydh2Kd > 1)
                    {
                        sImportZydh += "-" + (string)dtZydh.DefaultView[i - 1]["装入代号"];
                    }
                    sImportZydh += "," + (string)dtZydh.DefaultView[i]["装入代号"];
                    sZydh1 = (string)dtZydh.DefaultView[i]["装入代号1"];
                    iZydh2 = (int)dtZydh.DefaultView[i]["装入代号2"];
                    iZydh2Kd = 1;
                }
                else
                {
                    iZydh2Kd++;
                }
            }
            if (iZydh2Kd > 1)
            {
                sImportZydh += "-" + (string)dtZydh.DefaultView[dtZydh.DefaultView.Count - 1]["装入代号"];
            }
            return sImportZydh;
        }

        /// <summary>
        /// 导入产品设计明细表
        /// </summary>
        public static string ImportCpSjMx(int iCpSjId, FileUpload fUpload, string sBjr)
        {
            if (fUpload.PostedFile == null || fUpload.PostedFile.FileName == string.Empty)
            {
                return "导入产品设计明细表失败，可能格式不正确！";
            }
            string sFileName = string.Format("{0}Ls{1}{2}.tmp", BGlobal.AttachmentPath, fUpload.Page.Session.SessionID, DateTime.Now.ToString("yyyyMMddHHmmssffffff"));
            try
            {
                fUpload.PostedFile.SaveAs(sFileName);
            }
            catch
            {
                return "导入产品设计明细表失败，可能格式不正确！";
            }
            DataSet dsZysl = null;
            DataSet dsZydh = null;
            using (DOleDb da = new DOleDb("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + sFileName + "\";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\""))
            {
                dsZysl = da.ExecuteQuery("SELECT [Part Field 1] AS 代号,Count(1) AS 装入数量 FROM [Sheet1$] WHERE ([Part Field 1] LIKE '1.%' OR [Part Field 1] LIKE '2.%' OR [Part Field 1] LIKE 'HIAU%') GROUP BY [Part Field 1]");
                dsZydh = da.ExecuteQuery("SELECT [Part Field 1] AS 代号,Designator AS 装入代号,'' AS 装入代号1,-9 AS 装入代号2 FROM [Sheet1$] WHERE ([Part Field 1] LIKE '1.%' OR [Part Field 1] LIKE '2.%' OR [Part Field 1] LIKE 'HIAU%')");
            }
            if (dsZysl == null || dsZydh == null)
            {
                return "导入产品设计明细表失败，可能格式不正确！";
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if ((int)dsZysl.Tables[0].Compute("Count(代号)", "代号 like 'HIAU%'") > 0)
                {
                    da.Execute("UPDATE 产品明细 SET 编辑标志=0 WHERE 编辑标志<>0 AND 产品ID=@P0 AND EXISTS(SELECT 1 FROM 产品设计 A WHERE A.ID=@P0 AND A.状态=0)", iCpSjId);
                }
                if ((int)dsZysl.Tables[0].Compute("Count(代号)", "代号 like '1.%' or 代号 like '2.%'") > 0)
                {
                    da.Execute("UPDATE 产品外购 SET 编辑标志=0 WHERE 编辑标志<>0 AND 产品ID=@P0 AND EXISTS(SELECT 1 FROM 产品设计 A WHERE A.ID=@P0 AND A.状态=0)", iCpSjId);
                }
            }
            string sErrDh = string.Empty;
            for (int i = 0; i < dsZysl.Tables[0].Rows.Count; i++)
            {
                int iZysl = (int)dsZysl.Tables[0].Rows[i]["装入数量"];
                string sZydh = ImportCpSjMxZydh((string)dsZysl.Tables[0].Rows[i]["代号"], dsZydh.Tables[0]);
                if (iZysl == 1 && sZydh != string.Empty)
                {
                    string[] sArrZydh = sZydh.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
                    iZysl = sArrZydh.Length;
                    for (int j = 0; j < sArrZydh.Length; j++)
                    {
                        string[] sArrZydhs = sArrZydh[j].Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries);
                        if (sArrZydhs.Length == 2 && Regex.Replace(sArrZydhs[0], @"\d+$", "") != sArrZydhs[0] && Regex.Replace(sArrZydhs[0], @"\d+$", "").ToUpper() == Regex.Replace(sArrZydhs[1], @"\d+$", "").ToUpper())
                        {
                            iZysl += Math.Abs(Convert.ToInt32(sArrZydhs[1].Replace(Regex.Replace(sArrZydhs[1], @"\d+$", ""), "")) - Convert.ToInt32(sArrZydhs[0].Replace(Regex.Replace(sArrZydhs[0], @"\d+$", ""), "")));
                        }
                    }
                }
                if (!ImportCpSjMxDb(iCpSjId, (string)dsZysl.Tables[0].Rows[i]["代号"], iZysl, sZydh, sBjr))
                {
                    sErrDh += (string)dsZysl.Tables[0].Rows[i]["代号"] + "、";
                }
            }
            if (sErrDh != string.Empty)
            {
                return string.Format("导入部分代号{0}失败！", sErrDh.Substring(0, sErrDh.Length - 1));
            }
            return "导入产品设计明细表成功，请在零部件明细或外购件明细中查看！";
        }

        /// <summary>
        /// 根据[产品设计ID]获得产品设计零部件
        /// </summary>
        public static DataSet GetCpSjLbjBjBySjId(int iCpSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.零件ID,B.代号,B.名称,B.规格型号,B.单位,A.装入数量,A.装入代号,A.备注,A.编辑人,A.编辑标志,CASE B.状态 WHEN 0 THEN '未提交' WHEN 1 THEN '已提交' ELSE '已审核' END 状态 FROM 产品明细 A,产品设计 B WHERE A.零件ID=B.ID AND A.产品ID=@P0 ORDER BY A.ID", iCpSjId);
            }
        }

        /// <summary>
        /// 根据[产品设计ID]获得产品设计零部件
        /// </summary>
        public static DataSet GetCpSjLbjBySjId(int iCpSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.零件ID,B.代号,B.名称,B.规格型号,B.单位,A.装入数量,A.装入代号,A.备注,A.编辑人,A.编辑标志,CASE B.状态 WHEN 0 THEN '未提交' WHEN 1 THEN '已提交' ELSE '已审核' END 状态 FROM 产品明细 A,产品设计 B WHERE A.零件ID=B.ID AND A.产品ID=@P0 ORDER BY B.代号", iCpSjId);
            }
        }

        /// <summary>
        /// 添加产品设计零部件时循环引用判断
        /// </summary>
        private static bool IsParentCpSjLbj(int iCpSjId, int iCpSjLbjId)
        {
            if (iCpSjLbjId == iCpSjId)
            {
                return true;
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                DataSet ds = da.ExecuteQuery("SELECT 产品ID FROM 产品明细 WHERE 零件ID=@P0", iCpSjId);
                if (ds == null)
                {
                    return true;
                }
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ds.Tables[0].DefaultView.RowFilter = "产品ID=" + iCpSjLbjId.ToString();
                    if (ds.Tables[0].DefaultView.Count > 0)
                    {
                        return true;
                    }
                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        if (IsParentCpSjLbj((int)ds.Tables[0].Rows[i][0], iCpSjLbjId))
                        {
                            return true;
                        }
                    }
                    return false;
                }
                return false;
            }
        }

        /// <summary>
        /// 添加产品设计零部件
        /// </summary>
        public static string SaveCpSjLbjAdd(int iCpSjId, string[] sCpSjLbjDhArr, string sBjr)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("UPDATE 产品设计 SET 编辑人=@P1,编辑时间=@P2 WHERE ID=@P0 AND 状态=0 AND (SUBSTRING(代号,5,1) BETWEEN 1 AND 6)", iCpSjId, sBjr, DateTime.Now) <= 0)
                {
                    return "添加产品设计零部件失败，可能已提交或不允许添加！";
                }
                da.Execute("UPDATE 产品明细 SET 编辑标志=0 WHERE 编辑标志<>0 AND 产品ID=@P0", iCpSjId);
                for (int i = 0; i < sCpSjLbjDhArr.Length; i++)
                {
                    DataSet ds = da.ExecuteQuery("SELECT A.ID FROM 产品设计 A WHERE A.代号=@P1 AND NOT EXISTS(SELECT 1 FROM 产品明细 B WHERE B.产品ID=@P0 AND B.零件ID=A.ID) AND EXISTS(SELECT 1 FROM 产品设计 B WHERE B.ID=@P0 AND B.状态=0)", iCpSjId, sCpSjLbjDhArr[i]);
                    if (((ds != null) && (ds.Tables[0].Rows.Count == 1)) && !IsParentCpSjLbj(iCpSjId, (int)ds.Tables[0].Rows[0][0]))
                    {
                        da.Execute("INSERT INTO 产品明细(产品ID,零件ID,装入数量,装入代号,备注,编辑人,编辑时间,编辑标志) SELECT @P0,A.ID,1,'','',@P1,@P2,1 FROM 产品设计 A WHERE A.代号=@P3 AND NOT EXISTS(SELECT 1 FROM 产品明细 B WHERE B.产品ID=@P0 AND B.零件ID=A.ID) AND EXISTS(SELECT 1 FROM 产品设计 B WHERE B.ID=@P0 AND B.状态=0)", iCpSjId, sBjr, DateTime.Now, sCpSjLbjDhArr[i]);
                        if (IsParentCpSjLbj(iCpSjId, (int)ds.Tables[0].Rows[0][0]))
                        {
                            da.Execute("DELETE FROM 产品明细 WHERE 产品ID=@P0 AND 零件ID=@P1)", iCpSjId, (int)ds.Tables[0].Rows[0][0]);
                        }
                    }
                }
                return null;
            }
        }

        /// <summary>
        /// 替换产品设计零部件
        /// </summary>
        public static string SaveCpSjLbjTh(int iCpSjId, int iCpSjLbjId, string sCpSjLbjDh, string sBjr)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("UPDATE 产品设计 SET 编辑人=@P1,编辑时间=@P2 WHERE ID=@P0 AND 状态=0 AND (SUBSTRING(代号,5,1) BETWEEN 1 AND 6)", iCpSjId, sBjr, DateTime.Now) <= 0)
                {
                    return "替换产品设计零部件失败，可能已提交！";
                }
                da.Execute("UPDATE 产品明细 SET 编辑标志=0 WHERE 编辑标志<>0 AND 产品ID=@P0", iCpSjId);
                DataSet ds = da.ExecuteQuery("SELECT A.ID FROM 产品设计 A WHERE A.代号=@P1 AND NOT EXISTS(SELECT 1 FROM 产品明细 B WHERE B.产品ID=@P0 AND B.零件ID=A.ID) AND EXISTS(SELECT 1 FROM 产品设计 B WHERE B.ID=@P0 AND B.状态=0)", iCpSjId, sCpSjLbjDh);
                if (((ds != null) && (ds.Tables[0].Rows.Count == 1)) && !IsParentCpSjLbj(iCpSjId, (int)ds.Tables[0].Rows[0][0]))
                {
                    da.Execute("UPDATE 产品明细 SET 零件ID=(SELECT A.ID FROM 产品设计 A WHERE A.代号=@P2),编辑人=@P3,编辑时间=@P4,编辑标志=1 WHERE ID=@P1 AND 产品ID=@P0 AND EXISTS(SELECT 1 FROM 产品设计 A WHERE A.代号=@P2 AND NOT EXISTS(SELECT 1 FROM 产品明细 B WHERE B.产品ID=@P0 AND B.零件ID=A.ID) AND EXISTS(SELECT 1 FROM 产品设计 B WHERE B.ID=@P0 AND B.状态=0))", iCpSjId, iCpSjLbjId, sCpSjLbjDh, sBjr, DateTime.Now);
                    if (IsParentCpSjLbj(iCpSjId, (int)ds.Tables[0].Rows[0][0]))
                    {
                        da.Execute("DELETE FROM 产品明细 WHERE 产品ID=@P0 AND 零件ID=@P1)", iCpSjId, (int)ds.Tables[0].Rows[0][0]);
                    }
                }
                return null;
            }
        }

        /// <summary>
        /// 保存产品设计零部件
        /// </summary>
        public static string SaveCpSjLbj(int iCpSjId, string sMc, string sGgxh, string sDw, string sBz, string sBjr, GridView gCpSjLbj)
        {
            if (sMc.Trim() == string.Empty || sDw.Trim() == string.Empty)
            {
                return "产品设计的名称、计量单位不能为空！";
            }
            for (int i = 0; i < gCpSjLbj.Rows.Count - 1; i++)
            {
                if (!BGlobal.IsNumeric(((TextBox)gCpSjLbj.Rows[i].Cells[5].Controls[1]).Text.Trim()) || Convert.ToInt32(((TextBox)gCpSjLbj.Rows[i].Cells[5].Controls[1]).Text.Trim()) <= 0)
                {
                    return "产品设计零部件的装入数量必须大于零！";
                }
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("UPDATE 产品设计 SET 名称=@P1,规格型号=@P2,单位=@P3,备注=@P4,编辑人=@P5,编辑时间=@P6 WHERE ID=@P0 AND 状态=0 AND (SUBSTRING(代号,5,1) BETWEEN 1 AND 6)", iCpSjId, sMc.Trim(), sGgxh.Trim(), sDw.Trim(), sBz.Trim(), sBjr, DateTime.Now) <= 0)
                {
                    return "保存产品设计零部件失败，可能已提交！";
                }
                da.Execute("UPDATE 产品明细 SET 编辑标志=0 WHERE 编辑标志<>0 AND 产品ID=@P0", iCpSjId);
                for (int i = 0; i < gCpSjLbj.Rows.Count - 1; i++)
                {
                    da.Execute("UPDATE 产品明细 SET 装入数量=@P1,装入代号=@P2,编辑人=@P3,编辑时间=@P4,编辑标志=1 WHERE ID=@P0 AND (装入数量<>@P1 OR 装入代号<>@P2)", (int)gCpSjLbj.DataKeys[i].Value, Convert.ToInt32(((TextBox)gCpSjLbj.Rows[i].Cells[5].Controls[1]).Text.Trim()), ((TextBox)gCpSjLbj.Rows[i].Cells[6].Controls[1]).Text.Trim(), sBjr, DateTime.Now);
                }
                return null;
            }
        }

        /// <summary>
        /// 根据[产品设计零部件ID]移除产品设计零部件
        /// </summary>
        public static string DelCpSjLbj(int iCpSjLbjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("DELETE FROM 产品明细 WHERE ID=@P0 AND EXISTS(SELECT 1 FROM 产品设计 WHERE 产品设计.ID=产品明细.产品ID AND 产品设计.状态=0)", iCpSjLbjId) <= 0)
                {
                    return "移除产品设计零部件失败，可能已提交！";
                }
                return null;
            }
        }

        /// <summary>
        /// 根据[产品设计ID]获得产品设计外购件
        /// </summary>
        public static DataSet GetCpSjWgjBjBySjId(int iCpSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.购件ID,B.代号,B.名称,B.规格型号,B.单位,A.装入数量,A.装入代号,A.备注,A.编辑人,A.编辑标志 FROM 产品外购 A,购件目录 B WHERE A.购件ID=B.ID AND A.产品ID=@P0 ORDER BY A.ID", iCpSjId);
            }
        }

        /// <summary>
        /// 根据[产品设计ID]获得产品设计外购件
        /// </summary>
        public static DataSet GetCpSjWgjBySjId(int iCpSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.购件ID,B.代号,B.名称,B.规格型号,B.单位,A.装入数量,A.装入代号,A.备注,A.编辑人,A.编辑标志 FROM 产品外购 A,购件目录 B WHERE A.购件ID=B.ID AND A.产品ID=@P0 ORDER BY B.代号", iCpSjId);
            }
        }

        /// <summary>
        /// 添加产品设计外购件
        /// </summary>
        public static string SaveCpSjWgjAdd(int iCpSjId, string[] sCpSjWgjDhArr, string sBjr)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("UPDATE 产品设计 SET 编辑人=@P1,编辑时间=@P2 WHERE ID=@P0 AND 状态=0 AND (SUBSTRING(代号,5,1) BETWEEN 1 AND 4)", iCpSjId, sBjr, DateTime.Now) <= 0)
                {
                    return "添加产品设计外购件失败，可能已提交！";
                }
                da.Execute("UPDATE 产品外购 SET 编辑标志=0 WHERE 编辑标志<>0 AND 产品ID=@P0", iCpSjId);
                for (int i = 0; i < sCpSjWgjDhArr.Length; i++)
                {
                    da.Execute("INSERT INTO 产品外购(产品ID,购件ID,装入数量,装入代号,备注,编辑人,编辑时间,编辑标志) SELECT @P0,A.ID,1,'','',@P1,@P2,1 FROM 购件目录 A WHERE A.代号=@P3 AND NOT EXISTS(SELECT 1 FROM 产品外购 B WHERE B.产品ID=@P0 AND B.购件ID=A.ID) AND EXISTS(SELECT 1 FROM 产品设计 B WHERE B.ID=@P0 AND B.状态=0)", iCpSjId, sBjr, DateTime.Now, sCpSjWgjDhArr[i]);
                }
                return null;
            }
        }

        /// <summary>
        /// 替换产品设计外购件
        /// </summary>
        public static string SaveCpSjWgjTh(int iCpSjId, int iCpSjWgjId, string sCpSjWgjDh, string sBjr)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("UPDATE 产品设计 SET 编辑人=@P1,编辑时间=@P2 WHERE ID=@P0 AND 状态=0 AND (SUBSTRING(代号,5,1) BETWEEN 1 AND 4)", iCpSjId, sBjr, DateTime.Now) <= 0)
                {
                    return "替换产品设计外购件失败，可能已提交！";
                }
                da.Execute("UPDATE 产品外购 SET 编辑标志=0 WHERE 编辑标志<>0 AND 产品ID=@P0", iCpSjId);
                da.Execute("UPDATE 产品外购 SET 购件ID=(SELECT A.ID FROM 购件目录 A WHERE A.代号=@P2),编辑人=@P3,编辑时间=@P4,编辑标志=1 WHERE ID=@P1 AND 产品ID=@P0 AND EXISTS(SELECT 1 FROM 购件目录 A WHERE A.代号=@P2 AND NOT EXISTS(SELECT 1 FROM 产品外购 B WHERE B.产品ID=@P0 AND B.购件ID=A.ID) AND EXISTS(SELECT 1 FROM 产品设计 B WHERE B.ID=@P0 AND B.状态=0))", iCpSjId, iCpSjWgjId, sCpSjWgjDh, sBjr, DateTime.Now);
                return null;
            }
        }

        /// <summary>
        /// 保存产品设计外购件
        /// </summary>
        public static string SaveCpSjWgj(int iCpSjId, string sMc, string sGgxh, string sDw, string sBz, string sBjr, GridView gCpSjWgj)
        {
            if (sMc.Trim() == string.Empty || sDw.Trim() == string.Empty)
            {
                return "产品设计的名称、计量单位不能为空！";
            }
            for (int i = 0; i < gCpSjWgj.Rows.Count - 1; i++)
            {
                if (!BGlobal.IsNumeric(((TextBox)gCpSjWgj.Rows[i].Cells[5].Controls[1]).Text.Trim()) || Convert.ToInt32(((TextBox)gCpSjWgj.Rows[i].Cells[5].Controls[1]).Text.Trim()) <= 0)
                {
                    return "产品设计外购件的装入数量必须大于零！";
                }
            }
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("UPDATE 产品设计 SET 名称=@P1,规格型号=@P2,单位=@P3,备注=@P4,编辑人=@P5,编辑时间=@P6 WHERE ID=@P0 AND 状态=0 AND (SUBSTRING(代号,5,1) BETWEEN 1 AND 4)", iCpSjId, sMc.Trim(), sGgxh.Trim(), sDw.Trim(), sBz.Trim(), sBjr, DateTime.Now) <= 0)
                {
                    return "保存产品设计外购件失败，可能已提交！";
                }
                da.Execute("UPDATE 产品外购 SET 编辑标志=0 WHERE 编辑标志<>0 AND 产品ID=@P0", iCpSjId);
                for (int i = 0; i < gCpSjWgj.Rows.Count - 1; i++)
                {
                    da.Execute("UPDATE 产品外购 SET 装入数量=@P1,装入代号=@P2,编辑人=@P3,编辑时间=@P4,编辑标志=1 WHERE ID=@P0 AND (装入数量<>@P1 OR 装入代号<>@P2)", (int)gCpSjWgj.DataKeys[i].Value, Convert.ToInt32(((TextBox)gCpSjWgj.Rows[i].Cells[5].Controls[1]).Text.Trim()), ((TextBox)gCpSjWgj.Rows[i].Cells[6].Controls[1]).Text.Trim(), sBjr, DateTime.Now);
                }
                return null;
            }
        }

        /// <summary>
        /// 根据[产品设计外购件ID]移除产品设计外购件
        /// </summary>
        public static string DelCpSjWgj(int iCpSjWgjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                if (da.Execute("DELETE FROM 产品外购 WHERE ID=@P0 AND EXISTS(SELECT 1 FROM 产品设计 WHERE 产品设计.ID=产品外购.产品ID AND 产品设计.状态=0)", iCpSjWgjId) <= 0)
                {
                    return "移除产品设计外购件失败，可能已提交！";
                }
                return null;
            }
        }

        #endregion
    }
}
